Hi I have problem with calling store procedure.

when i am trying to call my procedure from my following java code

connection = ConnectionManager.getInstance().getConnection(dataBaseURL, serverName, portNumber, sid, username, password);

callable = connection.prepareCall("{call SCHEMA_RESTORE.restore()}");

callable.executeUpdate();

..................................

I am getting this Exception

Exception in thread "main" java.sql.SQLException: ORA-06550: line 1, column 7:

PLS-00201: identifier 'SCHEMA_RESTORE.RESTORE' must be declared

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)

at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)

at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)

at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)

at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1093)

at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2047)

at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1940)

at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2709)

at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:589)

at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:656)

My Prodedure is

CREATE OR REPLACE PACKAGE BODY SCHEMA_RESTORE IS

PROCEDURE backup (pbRecreateBackupTable IN BOOLEAN DEFAULT FALSE)

IS

TableAlreadyExists EXCEPTION;

PRAGMA EXCEPTION_INIT (TableAlreadyExists, -955);

nCount NUMBER;

CURSOR cTables IS SELECT tbls.table_name tbl, SUBSTR(tbls.table_name,4) name FROM user_tables tbls, FT_T_TBDF tbdf

WHERE tbls.table_name NOT LIKE gsPrefix || '%' AND tbls.table_name NOT LIKE 'FT_CFG%' AND tbls.table_name NOT IN ('FT_WF_APEV','FT_WF_WFTJ','FT_WF_WFTN','FT_WF_WFNP','FT_WF_WFNV','FT_WF_WFIO','FT_WF_WFGV','FT_WF_WFND','FT_WF_WFDF','EXCEPTIONS','TESTDSFP') and tbls.table_name NOT LIKE 'FT_LOG%'

AND tbdf.tbl_id(+) = SUBSTR(tbls.table_name,-4) AND tbdf.tbl_desc NOT LIKE '%Note: This table is obsolete%';

BEGIN

RAISE;

END;

END LOOP;

EXCEPTION

WHEN OTHERS THEN

RAISE;

END backup;

解决方案

The error message says that SCHEMA_RESTORE.RESTORE cannot be found. There are several possible causes:

The package (and procedure) are in a different schema, e.g. you compiled it as user A but are trying to call them as user B.

You don't have the access right to execute procedures from the package. Thus it becomes invisible.

You have defined the procedure in the package body, but haven't declared it in the package header.

A further problem I can see are the parentheses. If you call a procedure without arguments, the prentheses shoul be omitted:

{ call SCHEMA_RESTORE.restore }